package com.sx.util.poi;
import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.sx.feesunit.entity.UserUnitEntity;


/**
 * Excel 读取（2007+新格式）
 * @author	chengesheng
 * @date	2012-4-27 下午03:39:01
 * @note	PoiExcel2k7Helper
 */
public class PoiExcel2k7Helper extends PoiExcelHelper {
	private Log   log = LogFactory.getLog(this.getClass());
	/**
	 * 获取2007的Excel返回 HSSFWorkbook
	 **/
	public XSSFWorkbook getWorkingExcel(File file){
		XSSFWorkbook wb = null;
		try {
			wb = new XSSFWorkbook(new FileInputStream(file));
		} catch (Exception e) {
			log.error("获取Excel2007数据文件失败请查明原因！",e);
		}
		return wb;
	}
	
	/**
	 * 按照sheet获取数据信息
	 **/
	public List<UserUnitEntity> findUserInfos(File file){
		List<UserUnitEntity> list = new ArrayList<UserUnitEntity>();
		try {
			XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(file));
			XSSFSheet sheet = wb.getSheetAt(0);
			int last = sheet.getLastRowNum();
			int first = sheet.getFirstRowNum();
			/*System.out.println("last====>"+last);
			System.out.println("first====>"+first);*/
			for (int i = 0; i < last-first; i++) {
				XSSFRow row = sheet.getRow(i);
				if(row==null){break;}
				short firstcell = row.getFirstCellNum();
				short lastcell = row.getLastCellNum();
				String str = "";
				for (int j = 0; j < lastcell-firstcell; j++) {
					XSSFCell cell = row.getCell(j);
					//得到单元格类型
					if(cell==null){
						str += "";
					}else{
						int type = cell.getCellType();
						switch(type){
							case XSSFCell.CELL_TYPE_BLANK:
								str += cell.getStringCellValue();
								break;
							case XSSFCell.CELL_TYPE_BOOLEAN:
								str += cell.getBooleanCellValue();
								break;
							case XSSFCell.CELL_TYPE_ERROR:
								str += cell.getErrorCellValue();
								break;
							case XSSFCell.CELL_TYPE_FORMULA:
								str += cell.getCellFormula();
								break;
							case XSSFCell.CELL_TYPE_STRING:
								str += cell.getStringCellValue();
								break;
							case XSSFCell.CELL_TYPE_NUMERIC:
								str += cell.getNumericCellValue();
								break;
						}
					}
					str += "\t";
				}
				System.out.println("====>"+str);
				/*String str = "";
				for (int j = 0; j < lastcell-firstcell; j++) {
					XSSFCell cell = row.getCell(j);
					System.out.println("====>"+cell.getStringCellValue());
					str += " " + cell.getStringCellValue();
				}
				System.out.println("====>"+str);*/
			}
		} catch (Exception e) {
			e.printStackTrace();
			log.error("获取Excel2007数据文件失败数据信息出错!",e);
			// TODO: handle exception
		}
		return list;
	}
	
	/** 获取sheet列表 */
	public ArrayList<String> getSheetList(String filePath) {
		ArrayList<String> sheetList = new ArrayList<String>(0);
		try {
			XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(filePath));
			Iterator<XSSFSheet> iterator = wb.iterator();
			while (iterator.hasNext()) {
				sheetList.add(iterator.next().getSheetName());
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return sheetList;
	}

	/** 读取Excel文件内容 */
	public ArrayList<ArrayList<String>> readExcel(String filePath, int sheetIndex, String rows, String columns) {
		ArrayList<ArrayList<String>> dataList = new ArrayList<ArrayList<String>> ();
		try {
			XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(filePath));
			XSSFSheet sheet = wb.getSheetAt(sheetIndex);
			
			dataList = readExcel(sheet, rows, getColumnNumber(sheet, columns));
		} catch (Exception e) {
			e.printStackTrace();
		}
		return dataList;
	}
	
	/** 读取Excel文件内容 */
	public ArrayList<ArrayList<String>> readExcel(String filePath, int sheetIndex, String rows, int[] cols) {
		ArrayList<ArrayList<String>> dataList = new ArrayList<ArrayList<String>> ();
		try {
			XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(filePath));
			XSSFSheet sheet = wb.getSheetAt(sheetIndex);
			
			dataList = readExcel(sheet, rows, cols);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return dataList;
	}
}